﻿using System;
using System.Data;
using System.Data.OleDb;

namespace Utility4Net.Data
{
    /// <summary>
    /// Microsoft Access数据库操作类
    /// </summary>
    public class AccessHelper
    {
        protected static OleDbConnection conn = new OleDbConnection();
        protected static OleDbCommand comm = new OleDbCommand();
        private string ConnectionString = string.Empty;
        public AccessHelper()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 构造函数,初始化字符串连接串
        /// </summary>
        /// <param name="sConn">字符串连接串,like"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=1.mdb"</param>
        public AccessHelper(string sConn)
        {
            ConnectionString = sConn;
        }
        /// <summary> 
        /// 打开数据库连接 
        /// </summary> 
        private void openConnection()
        {
            if (conn.State == ConnectionState.Closed )
            {
                conn.ConnectionString = ConnectionString;
                comm.Connection = conn;
                try
                {
                    conn.Open();
                    
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
        /// <summary> 
        /// 关闭当前数据库连接 
        /// </summary> 
        private void closeConnection()
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        /// <summary> 
        /// 执行Sql查询语句 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public void ExecuteSql(string sqlstr)
        {
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }

        /// <summary> 
        /// 执行Sql更新语句 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>布尔值</returns>
        public bool ExecuteUpdate(string sqlstr)
        {
            int isUpdateOk = 0;
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                isUpdateOk = Convert.ToInt32(comm.ExecuteNonQuery());
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            if (isUpdateOk > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询数据，取得数据行
        /// </summary>
        /// <param name="strSQL">数据库连接串</param>
        /// <returns>第一行</returns>
        public DataRow GetDataRow(string strSQL)
        {
            try
            {
                openConnection();
                OleDbDataAdapter OleAdp = new OleDbDataAdapter(strSQL, conn);
                DataSet Rs = new DataSet();
                OleAdp.Fill(Rs);
                if (Rs.Tables[0].Rows.Count != 0)
                    return Rs.Tables[0].Rows[0];
                else
                    return null;
            }
            catch
            {
                return null;
            }

        }
        /// <summary>
        /// 查询数据，取得数据视图
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataView GetDataView(string strSQL)
        {
            try
            {
                openConnection();
                OleDbDataAdapter OleAdp = new OleDbDataAdapter(strSQL, conn);
                DataSet Rs = new DataSet();
                OleAdp.Fill(Rs);
                return Rs.Tables[0].DefaultView;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

        }
        /// <summary>
        /// 查询是否存在
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public bool SearchTable(string strSQL)
        {
            OleDbDataReader OleDr = null;
            try
            {
                openConnection();
                comm.CommandText = strSQL;
                comm.CommandType = CommandType.Text;
                OleDr = comm.ExecuteReader(CommandBehavior.CloseConnection);
                if (OleDr.Read())
                {

                    return true;
                }
                else
                {

                    return false;
                }
            }
            catch
            {
                try
                {
                    OleDr.Close();
                    closeConnection();
                }
                catch
                {
                }
                return false;
            }
            finally
            {
                if (OleDr != null)
                OleDr.Close();
            }

        }
        /// <summary>
        /// 查找
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public string SearchValue(string strSQL)
        {
            OleDbDataReader OleDr = null;
            try
            {
                openConnection();
                comm.CommandText = strSQL;
                comm.CommandType = CommandType.Text;
                OleDr = comm.ExecuteReader(CommandBehavior.CloseConnection);
                if (OleDr.Read())
                {

                    return OleDr[0].ToString();
                }
                else
                    return "";
            }
            catch
            {
                try
                {
                    OleDr.Close();
                    closeConnection();
                }
                catch
                {
                }
                return "";
            }
            finally
            {
                if (OleDr!=null)
                OleDr.Close();
            }

        }
        
        /// <summary> 
        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>object 返回值 </returns> 
        public object ExecuteScalar(string sqlstr)
        {
            object obj = new object();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                obj = comm.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return obj;
        }

        /// <summary> 
        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
        /// 方法关闭数据库连接 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>SqlDataReader对象</returns> 
        public OleDbDataReader dataReader(string sqlstr)
        {
            OleDbDataReader dr = null;
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    dr.Close();
                    closeConnection();
                }
                catch
                {
                }
            }
            return dr;
        }

        ///<summary>
        ///关闭datareader
        ///传入SqlDataReader的ref
        ///</summary>
        public void closeDataReader(ref OleDbDataReader sdr)
        {
            try
            {
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception)
            { }
        }

        /// <summary> 
        /// 返回指定Sql语句的DataSet 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataSet</returns> 
        public DataSet dataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return ds;
        }

        /// <summary> 
        /// 返回指定Sql语句的DataSet 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="ds">传入的引用DataSet对象</param> 
        public void dataSet(string sqlstr, ref DataSet ds)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            finally
            {
                closeConnection();
            }
        }
        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public DataTable dataTable(string sqlstr)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception)
            {
            }
            finally
            {
                closeConnection();
            }
            return datatable;
        }

        /// <summary> 
        /// 执行指定Sql语句,同时给传入DataTable进行赋值 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="dt">ref DataTable dt </param> 
        public void dataTable(string sqlstr, ref DataTable dt)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
        }

        /// <summary> 
        /// 执行指定Sql语句 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public DataView dataView(string sqlstr)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataView dv = new DataView();
            DataSet ds = new DataSet();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
                dv = ds.Tables[0].DefaultView;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return dv;
        }

        /// <summary> 
        /// 执行指定Sql语句,开始记录位置,和返回记录数 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="StartIndex">开始记录位置</param>
        /// <param name="pageSize">返回记录数</param> 
        public DataView dataView(string sqlstr, int StartIndex, int pageSize)
        {
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataView dv = new DataView();
            DataSet ds = new DataSet();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds, StartIndex, pageSize, "pagelist");
                dv = ds.Tables["pagelist"].DefaultView;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return dv;
        }
    }
}
